ALTER proc sp_split_line_item
@sales_order_id int, @line_item_id int, @qty_to_split int, @last_line_item_flag int, @first_line_item_flag int, @user_id varchar(20)

AS

begin	

declare  @LineItem_ID int, @SalesOrder_ID int, @COOL_Line_Item_No int, @Product_ID int, @COOL_Qty int,
	 @COOL_Unit_Cost float, @COOL_Amount float, @COOL_Requested_Date datetime, @COOL_Booking_Date datetime, @COOL_Sugg_Schedule_Date_With_Substitutables datetime,
	 @COOL_Sugg_Schedule_Date_Without_Substitutables datetime, @COOL_Schedule_Date datetime, @COOL_Revised_Date datetime, @COOL_Is_Cancel int, @COOL_Status varchar(50), 
	 @COOL_Added_By_ID varchar(20),@COOL_Added_By_Name varchar(50), @COOL_Last_Updated_By_ID varchar(20), @COOL_Last_Updated_By_Name varchar(50), @Timestamp datetime, 
	 @COOL_Taxable int, @COOL_Description varchar(200), @COOL_PO_Line_Item_No int,@ChangeNo int, @New_Line_Item_Status varchar(50), @COOL_Is_Expedite int, @COOL_CUSTOMER_PARTNUMBER varchar(50)
DECLARE @COOL_CUSTOM_PRICE_FIELD1 float
DECLARE @COOL_CUSTOM_TEXT_FIELD1 varchar(1000)
 
	declare lineItemCursor cursor for
	SELECT LineItem_ID, SalesOrder_ID, COOL_Line_Item_No, Product_ID, COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, COOL_Booking_Date, COOL_Sugg_Schedule_Date_With_Substitutables, COOL_Sugg_Schedule_Date_Without_Substitutables, COOL_Schedule_Date, COOL_Revised_Date, COOL_Is_Cancel, COOL_Status, COOL_Added_By_ID, COOL_Added_By_Name, COOL_Last_Updated_By_ID, COOL_Last_Updated_By_Name, Timestamp, COOL_Taxable, COOL_Description, COOL_PO_Line_Item_No, COOL_Is_Expedite, COOL_CUSTOMER_PARTNUMBER, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1 FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @line_item_id
	
	open lineItemCursor
	fetch next from  lineItemCursor into @LineItem_ID, @SalesOrder_ID, @COOL_Line_Item_No, @Product_ID, @COOL_Qty,
		@COOL_Unit_Cost, @COOL_Amount, @COOL_Requested_Date, @COOL_Booking_Date, @COOL_Sugg_Schedule_Date_With_Substitutables, @COOL_Sugg_Schedule_Date_Without_Substitutables,
		@COOL_Schedule_Date, @COOL_Revised_Date, @COOL_Is_Cancel, @COOL_Status, @COOL_Added_By_ID,
		@COOL_Added_By_Name, @COOL_Last_Updated_By_ID, @COOL_Last_Updated_By_Name, @Timestamp, @COOL_Taxable, @COOL_Description, @COOL_PO_Line_Item_No, @COOL_Is_Expedite, @COOL_CUSTOMER_PARTNUMBER, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1
 
	declare @total_shipped_issued_qty int
	declare @tempLineItem_ID int

	set @total_shipped_issued_qty = 0

	if @@fetch_status = 0
	begin
		
		select @total_shipped_issued_qty = sum(CSLIT_Qty_Shipped) from Customer_Shipping_Line_Item_Table CSLIT where LineItem_ID = @LineItem_ID and CSLIT_STATUS <> 'CANCELLED'
					
		if(@total_shipped_issued_qty is null)

		begin
			set @total_shipped_issued_qty = 0
		end		
		if ((@qty_to_split > 0) and (@qty_to_split < @COOL_Qty) and (@qty_to_split <= (@COOl_Qty - @total_shipped_issued_qty)) )
		begin

			declare @line_item_no int
			declare @cancel_line_item_no int

			select @line_item_no = max(COOL_Line_Item_No) from Customer_Open_Order_LineItem where SalesOrder_ID = @sales_order_id
			select @cancel_line_item_no = max(CCOL_Line_Item_No) FROM Customer_Cancel_Order_LineItem where SalesOrder_ID = @sales_order_id
			
			if @cancel_line_item_no IS NULL
			begin
				set @cancel_line_item_no = 0
			end

			if @cancel_line_item_no > @line_item_no
			begin
				set @line_item_no = @cancel_line_item_no
			end			

			set @line_item_no = @line_item_no + 1
			IF ( @COOL_Status = 'APPROVED' )
			 BEGIN
				set @New_Line_Item_Status = 'APPROVED'
			 END
			ELSE
			 BEGIN
				set @New_Line_Item_Status = 'HOLD'
			 END
			insert into Customer_Open_Order_LineItem(SalesOrder_ID, COOL_Line_Item_No, Product_ID, COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, COOL_Booking_Date, COOL_Sugg_Schedule_Date_With_Substitutables, COOL_Sugg_Schedule_Date_Without_Substitutables, COOL_Schedule_Date, COOL_Revised_Date, COOL_Is_Cancel, COOL_Status, COOL_Added_By_ID, COOL_Added_By_Name, COOL_Last_Updated_By_ID, COOL_Last_Updated_By_Name, Timestamp, COOL_Taxable, COOL_Description, COOL_PO_Line_Item_No, COOL_Is_Expedite, COOL_CUSTOMER_PARTNUMBER, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1 )
				values (@SalesOrder_ID, @line_item_no, @Product_ID, @COOL_Qty,
					@COOL_Unit_Cost, @COOL_Amount, @COOL_Requested_Date, @COOL_Booking_Date, @COOL_Sugg_Schedule_Date_With_Substitutables, @COOL_Sugg_Schedule_Date_Without_Substitutables,
					@COOL_Schedule_Date, @COOL_Revised_Date, @COOL_Is_Cancel, @New_Line_Item_Status, @COOL_Added_By_ID,
					@COOL_Added_By_Name, @COOL_Last_Updated_By_ID, @COOL_Last_Updated_By_Name, CURRENT_TIMESTAMP, @COOL_Taxable, @COOL_Description, @COOL_PO_Line_Item_No, @COOL_Is_Expedite, @COOL_CUSTOMER_PARTNUMBER, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)

			select @tempLineItem_ID = (SELECT @@IDENTITY)
			update Customer_Open_Order_LineItem set COOL_Qty = @qty_to_split, COOL_Amount = (@qty_to_split * @COOL_Unit_Cost) where LineItem_ID = @tempLineItem_ID
			
			update Customer_Open_Order_LineItem set COOL_Qty = (@COOL_Qty - @qty_to_split), COOL_Amount = ((@COOL_Qty - @qty_to_split) * @COOL_Unit_Cost) where LineItem_ID = @LineItem_ID

			declare @old_change_number int
			select @old_change_number =ChangeNo from Customer_Change_Order_LineItem where SalesOrder_ID = @salesorder_id and LineItem_ID = @lineItem_id
			if(@old_change_number is null)
			begin
				INSERT INTO Customer_Change_Order_LineItem (LineItem_ID, SalesOrder_ID,ChangeNo, CCO_Line_Item_No,CCO_PO_Line_Item_No,
				Product_ID, CCO_Qty, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, 
				CCO_Booking_Date,CCO_Sugg_Schedule_Date_With_Substitutables,
				CCO_Sugg_Schedule_Date_Without_Substitutables,CCO_Schedule_Date,
			        	CCO_Revised_Date,CCO_Taxable, Timestamp, CCO_CUSTOMER_PARTNUMBER, CCO_CUSTOM_PRICE_FIELD1, CCO_CUSTOM_TEXT_FIELD1)
				 (SELECT LineItem_ID, SalesOrder_ID, 0 ,COOL_Line_Item_No,COOL_PO_Line_Item_No,
					Product_ID, COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, 
					COOL_Booking_Date,COOL_Sugg_Schedule_Date_With_Substitutables,
					COOL_Sugg_Schedule_Date_Without_Substitutables,COOL_Schedule_Date,
					COOL_Revised_Date,COOL_Taxable, COOL_Booking_Date, COOL_CUSTOMER_PARTNUMBER, COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1 FROM Customer_Open_Order_LineItem cool
					WHERE LineItem_ID = @lineItem_id)	
			end

			SELECT @ChangeNo = MAX(ChangeNo) + 1 FROM customer_change_order_lineitem where LineItem_ID = @LineItem_ID
			INSERT INTO Customer_Change_Order_LineItem (LineItem_ID,SalesOrder_ID,ChangeNo,CCO_Line_Item_No, Product_ID, 
				CCO_Qty, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, CCO_Booking_Date, 
				CCO_Status, CCO_Change_By_ID, CCO_Change_By_Name,CCO_Taxable,CCO_PO_Line_Item_No, TimeStamp)
				(SELECT LineItem_ID,SalesOrder_ID, @ChangeNo,COOL_Line_Item_NO, Product_ID, (@COOL_Qty - @qty_to_split), @COOL_Unit_Cost, ((@COOL_Qty - @qty_to_split) * @COOL_Unit_Cost), COOL_Requested_Date, COOL_Booking_Date,@New_Line_Item_Status, @user_id, @user_id, COOL_Taxable, COOL_PO_Line_Item_No, COOL_Booking_Date  FROM Customer_Open_Order_LineItem where LineItem_ID = @LineItem_ID)
	
			declare @changeItem_id int
			SELECT @changeItem_Id = (SELECT @@IDENTITY)
			declare @change_number int
			SELECT @change_number = (SELECT MAX(CCO_Change_Number) FROM Customer_Change_Order_LineItem
			WHERE LineItem_Id = @LineItem_ID)
			if (@change_number is null)
				set @change_number = 1
			else
				set @change_number = @change_number + 1
			UPDATE Customer_Change_Order_LineItem 
			SET CCO_Change_Number = @change_number, CCO_Change_By_ID = @user_id,
			CCO_Change_By_Name = @user_id, TimeStamp = CURRENT_TIMESTAMP
			WHERE ChangeItem_ID = @changeItem_id

			INSERT INTO Customer_Change_Order_LineItem (LineItem_ID,SalesOrder_ID,ChangeNo,CCO_Line_Item_No, Product_ID, 
				CCO_Qty, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, CCO_Booking_Date, 
				CCO_Status, CCO_Change_By_ID, CCO_Change_By_Name,CCO_Taxable,CCO_PO_Line_Item_No, TimeStamp, CCO_CUSTOMER_PARTNUMBER, CCO_CUSTOM_PRICE_FIELD1, CCO_CUSTOM_TEXT_FIELD1)
				VALUES (@tempLineItem_ID,@SalesOrder_ID, 0,@line_item_no, @Product_ID, @qty_to_split, @COOL_Unit_Cost, (@qty_to_split * @COOL_Unit_Cost), 
				@COOL_Requested_Date, CURRENT_TIMESTAMP,@New_Line_Item_Status, @user_id, @user_id, @COOL_Taxable , @COOL_PO_Line_Item_No, CURRENT_TIMESTAMP, @COOL_CUSTOMER_PARTNUMBER, @COOL_CUSTOM_PRICE_FIELD1, @COOL_CUSTOM_TEXT_FIELD1)


			-- Update the SO_Status and SO_Is_Pending in the Sales Order table
			--Update only when the splitted line item is not approved
			IF ( @New_Line_Item_Status != 'APPROVED' )
			 BEGIN
				UPDATE Sales_Order SET SO_Status = 'PENDING',SO_Is_Pending = 0 WHERE SalesOrder_Id = @sales_order_id
			 END

			-- Insert the holds for the new Line Item in the Sales_Hold_Table
			INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, 
				Hold_By_User, Is_Approved, Timestamp) 
				VALUES(@tempLineItem_ID, @sales_order_id, '0','OPERATIONS','SYSTEM',1,CURRENT_TIMESTAMP)
			
			INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, 
				Hold_By_User, Is_Approved, Timestamp) 
				VALUES(@tempLineItem_ID, @sales_order_id, '0','MARKETING','SYSTEM',1,CURRENT_TIMESTAMP)
			
			INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, 
				Hold_By_User, Is_Approved, Timestamp) 
				VALUES(@tempLineItem_ID, @sales_order_id, '0','SALES','SYSTEM',1,CURRENT_TIMESTAMP)
			
			INSERT INTO Sales_Hold_Table(LineItem_ID, SalesOrder_ID, Hold_Reason_Code, Hold_By_Group, 
				Hold_By_User, Is_Approved, Timestamp) 
				VALUES(@tempLineItem_ID, @sales_order_id, '0','FINANCE','SYSTEM',1,CURRENT_TIMESTAMP)
			
			-- Approve the holds for the new line item if the line item is in approved status.
			IF ( @New_Line_Item_Status = 'APPROVED' )
			 BEGIN
				UPDATE Sales_Hold_Table SET Hold_Reason_Code = '', Is_Approved = 0 WHERE SalesOrder_ID = @sales_order_id AND LineItem_ID = @tempLineItem_ID
			 END

			-- Insert into Change_Sales_Order
			declare @change_no int	
			if (@last_line_item_flag = 1)
			begin
				select @change_no = max(ChangeNo) from Change_Sales_Order where SalesOrder_ID = @sales_order_id
				set @change_no = @change_no + 1

				INSERT INTO Change_Sales_Order(SalesOrder_ID,ChangeNo,Customer_ID,CSO_Customer_PO_Number,CSO_Contact_Person,
				CSO_Sales_Person,CSO_Shipping_Method,CSO_Shipping_Account,CSO_Billing_Address_ID,CSO_Shipping_Address_ID,
				CSO_Comments,CSO_Shipping_Instructions,CSO_EndUser,CSO_Status,CSO_Booking_Date,CSO_Is_Sample,CSO_Is_Cancel,
				CSO_Is_Pending,CSO_Is_Shipped,CSO_Entered_By_ID,CSO_Entered_By_Name,CSO_Sales_Tax_Type,CSO_Sub_Total,
				CSO_Sales_Tax,CSO_Frieght_Tax,CSO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
				CSO_Misc_Name,CSO_Show_Pro_Forma_Invoice,CSO_Reason)
			
				(SELECT SalesOrder_ID,@change_no,Customer_ID,SO_Customer_PO_Number,SO_Contact_Person,
				SO_Sales_Person,SO_Shipping_Method,SO_Shipping_Account,SO_Billing_Address_ID,SO_Shipping_Address_ID,
				SO_Comments,SO_Shipping_Instructions,SO_EndUser,SO_Status,SO_Booking_Date,SO_Is_Sample,SO_Is_Cancel,
				SO_Is_Pending,SO_Is_Shipped,SO_Entered_By_ID,SO_Entered_By_Name,SO_Sales_Tax_Type,SO_Sub_Total,
				SO_Sales_Tax,SO_Frieght_Tax,SO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
				SO_Misc_Name,SO_Show_Pro_Forma_Invoice,''
				FROM Sales_Order WHERE SalesOrder_id = @sales_order_id)
			end
			-- Insert into Sales_Audit_Trail 
			declare @ch_no int			
			select @ch_no = max(ChangeNo) from Change_Sales_Order where SalesOrder_ID = @sales_order_id


			declare @temp_line_item_no int
			select @temp_line_item_no=COOL_Line_Item_No from Customer_Open_Order_LineItem where LineItem_ID = @LineItem_ID
			
			if (@first_line_item_flag = 0)
			begin
				if (@last_line_item_flag = 0)
				begin
					set @ch_no = @ch_no + 1
				end
				insert into Sales_Audit_Trail(SalesOrder_ID, User_ID, Actions, Comments, TimeStamp) values(@sales_order_id, @user_id, 'Change Number is <b><i>'+ convert(varchar, @ch_no) +'</i></b><br> Line Item '+convert(varchar, @temp_line_item_no)+' has been split into two Line Items with quantities '+convert(varchar, (@COOL_Qty - @qty_to_split))+' and '+convert(varchar, @qty_to_split)+' respectively'+'<br>As a result Line Item '+ convert(varchar, @line_item_no) +' has been added with quantity '+ convert(varchar, @qty_to_split)+'^^^###', '', CURRENT_TIMESTAMP)				
			end
			else
			begin
				insert into Sales_Audit_Trail(SalesOrder_ID, User_ID, Actions, Comments, TimeStamp) values(@sales_order_id, @user_id, 'Line Item '+convert(varchar, @temp_line_item_no)+' has been split into two Line Items with quantities '+convert(varchar, (@COOL_Qty - @qty_to_split))+' and '+convert(varchar, @qty_to_split)+' respectively'+'<br>As a result Line Item '+ convert(varchar, @line_item_no) +' has been added with quantity '+ convert(varchar, @qty_to_split)+'^^^###', '', CURRENT_TIMESTAMP)				
			end			
		end
	end
	close lineItemCursor
	deallocate lineItemCursor	
end